"""数据库服务模块"""

from sqlalchemy import create_engine, inspect
from sqlalchemy.orm import sessionmaker, Session
from sqlalchemy.exc import SQLAlchemyError
import logging
import os
from datetime import datetime, timedelta
from typing import Dict, List, Optional, Any
from contextlib import contextmanager

from config import get_config
from .models import Base, Detection, Tracking, Violation, Alert, Camera, SystemLog, User, Config

class DatabaseService:
    """数据库服务，负责数据库连接、会话管理和数据操作"""
    
    def __init__(self):
        """初始化数据库服务"""
        # 获取数据库配置
        self.db_type = get_config('database.type', 'sqlite')
        
        if self.db_type == 'sqlite':
            # SQLite 配置
            db_path = get_config('database.sqlite.path', './data/traffic_system.db')
            # 确保数据目录存在
            os.makedirs(os.path.dirname(db_path), exist_ok=True)
            self.db_url = f'sqlite:///{db_path}'
        elif self.db_type == 'postgres':
            # PostgreSQL 配置
            host = get_config('database.postgres.host', 'localhost')
            port = get_config('database.postgres.port', 5432)
            database = get_config('database.postgres.database', 'traffic_system')
            user = get_config('database.postgres.user', 'admin')
            password = get_config('database.postgres.password', 'password')
            self.db_url = f'postgresql://{user}:{password}@{host}:{port}/{database}'
        elif self.db_type == 'mysql':
            # MySQL 配置
            host = get_config('database.mysql.host', 'localhost')
            port = get_config('database.mysql.port', 3306)
            database = get_config('database.mysql.database', 'traffic_system')
            user = get_config('database.mysql.user', 'admin')
            password = get_config('database.mysql.password', 'password')
            self.db_url = f'mysql+pymysql://{user}:{password}@{host}:{port}/{database}'
        else:
            raise ValueError(f"不支持的数据库类型: {self.db_type}")
        
        # 创建数据库引擎
        self.engine = create_engine(
            self.db_url,
            echo=get_config('database.echo', False),
            pool_pre_ping=True,
            pool_size=get_config('database.pool_size', 10),
            max_overflow=get_config('database.max_overflow', 20)
        )
        
        # 创建会话工厂
        self.SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=self.engine)
        
        # 设置日志
        self.logger = self._setup_logger()
        
        # 初始化数据库
        self._init_database()
        
        print("数据库服务已初始化")
    
    def _setup_logger(self) -> logging.Logger:
        """设置日志记录器
        
        Returns:
            日志记录器实例
        """
        logger = logging.getLogger('database_service')
        logger.setLevel(logging.INFO)
        
        # 添加控制台处理器
        console_handler = logging.StreamHandler()
        formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
        console_handler.setFormatter(formatter)
        logger.addHandler(console_handler)
        
        # 添加文件处理器
        log_file = get_config('database.log_file', './logs/database.log')
        log_dir = os.path.dirname(log_file)
        if log_dir:
            os.makedirs(log_dir, exist_ok=True)
        
        file_handler = logging.FileHandler(log_file)
        file_handler.setFormatter(formatter)
        logger.addHandler(file_handler)
        
        return logger
    
    def _init_database(self) -> None:
        """初始化数据库，创建所有表"""
        try:
            # 创建所有表
            Base.metadata.create_all(bind=self.engine)
            self.logger.info("数据库表创建成功")
            
            # 初始化默认数据
            self._init_default_data()
        except SQLAlchemyError as e:
            self.logger.error(f"数据库初始化失败: {str(e)}")
            raise
    
    def _init_default_data(self) -> None:
        """初始化默认数据"""
        with self.get_session() as session:
            try:
                # 检查是否已有用户
                existing_admin = session.query(User).filter(User.username == 'admin').first()
                
                if not existing_admin:
                    # 创建默认管理员用户
                    # 使用简单的密码哈希方式，避免bcrypt后端问题
                    # 注意：这只是临时解决方案，生产环境应使用安全的密码哈希
                    admin_user = User(
                        username='admin',
                        password_hash='admin123_hash',  # 临时哈希，生产环境应修改
                        email='admin@example.com',
                        role='admin',
                        status='active'
                    )
                    session.add(admin_user)
                    session.commit()
                    self.logger.info("创建默认管理员用户成功")
                
                # 初始化一些默认配置
                default_configs = [
                    {'key': 'system.name', 'value': '智能交通监管系统', 'description': '系统名称', 'config_type': 'general'},
                    {'key': 'system.version', 'value': '1.0.0', 'description': '系统版本', 'config_type': 'general'},
                    {'key': 'detection.confidence_threshold', 'value': 0.3, 'description': '检测置信度阈值', 'config_type': 'model'},
                    {'key': 'detection.iou_threshold', 'value': 0.45, 'description': '检测IOU阈值', 'config_type': 'model'},
                    {'key': 'tracking.max_age', 'value': 30, 'description': '目标最大消失帧数', 'config_type': 'tracking'},
                    {'key': 'tracking.n_init', 'value': 3, 'description': '目标初始化帧数', 'config_type': 'tracking'},
                    {'key': 'alerts.cooldown', 'value': 30.0, 'description': '告警冷却时间(秒)', 'config_type': 'alert'}
                ]
                
                for config_data in default_configs:
                    existing_config = session.query(Config).filter(Config.key == config_data['key']).first()
                    if not existing_config:
                        config = Config(**config_data)
                        session.add(config)
                
                session.commit()
                self.logger.info("默认配置初始化成功")
                
            except SQLAlchemyError as e:
                self.logger.error(f"默认数据初始化失败: {str(e)}")
                session.rollback()
    
    @contextmanager
    def get_session(self) -> Session:
        """获取数据库会话的上下文管理器
        
        Yields:
            数据库会话对象
        """
        session = self.SessionLocal()
        try:
            yield session
            session.commit()
        except SQLAlchemyError as e:
            session.rollback()
            self.logger.error(f"数据库操作失败: {str(e)}")
            raise
        finally:
            session.close()
    
    def execute_query(self, query_func, *args, **kwargs) -> Any:
        """执行数据库查询
        
        Args:
            query_func: 查询函数
            *args: 位置参数
            **kwargs: 关键字参数
            
        Returns:
            查询结果
        """
        with self.get_session() as session:
            return query_func(session, *args, **kwargs)
    
    # 摄像头相关操作
    def add_camera(self, camera_data: Dict) -> Camera:
        """添加摄像头
        
        Args:
            camera_data: 摄像头数据
            
        Returns:
            创建的摄像头对象
        """
        def _add_camera(session):
            camera = Camera(**camera_data)
            session.add(camera)
            session.flush()
            return camera
        
        return self.execute_query(_add_camera)
    
    def get_cameras(self, skip: int = 0, limit: int = 100, **filters) -> List[Camera]:
        """获取摄像头列表
        
        Args:
            skip: 跳过记录数
            limit: 返回记录数
            **filters: 过滤条件
            
        Returns:
            摄像头列表
        """
        def _get_cameras(session):
            query = session.query(Camera)
            if filters:
                query = query.filter_by(**filters)
            return query.offset(skip).limit(limit).all()
        
        return self.execute_query(_get_cameras)
    
    def update_camera(self, camera_id: int, update_data: Dict) -> Optional[Camera]:
        """更新摄像头信息
        
        Args:
            camera_id: 摄像头ID
            update_data: 更新数据
            
        Returns:
            更新后的摄像头对象，未找到则返回None
        """
        def _update_camera(session):
            camera = session.query(Camera).filter(Camera.id == camera_id).first()
            if camera:
                for key, value in update_data.items():
                    setattr(camera, key, value)
                session.flush()
            return camera
        
        return self.execute_query(_update_camera)
    
    # 检测相关操作
    def add_detection(self, detection_data: Dict) -> Detection:
        """添加检测记录
        
        Args:
            detection_data: 检测数据
            
        Returns:
            创建的检测对象
        """
        def _add_detection(session):
            detection = Detection(**detection_data)
            session.add(detection)
            session.flush()
            return detection
        
        return self.execute_query(_add_detection)
    
    def bulk_add_detections(self, detections_data: List[Dict]) -> int:
        """批量添加检测记录
        
        Args:
            detections_data: 检测数据列表
            
        Returns:
            添加的记录数
        """
        def _bulk_add_detections(session):
            detections = [Detection(**data) for data in detections_data]
            session.bulk_save_objects(detections)
            return len(detections)
        
        return self.execute_query(_bulk_add_detections)
    
    # 跟踪相关操作
    def add_tracking(self, tracking_data: Dict) -> Tracking:
        """添加跟踪记录
        
        Args:
            tracking_data: 跟踪数据
            
        Returns:
            创建的跟踪对象
        """
        def _add_tracking(session):
            tracking = Tracking(**tracking_data)
            session.add(tracking)
            session.flush()
            return tracking
        
        return self.execute_query(_add_tracking)
    
    def update_tracking(self, tracking_id: int, camera_id: int, update_data: Dict) -> Optional[Tracking]:
        """更新跟踪记录
        
        Args:
            tracking_id: 跟踪ID
            camera_id: 摄像头ID
            update_data: 更新数据
            
        Returns:
            更新后的跟踪对象，未找到则返回None
        """
        def _update_tracking(session):
            tracking = session.query(Tracking).filter(
                Tracking.tracking_id == tracking_id,
                Tracking.camera_id == camera_id,
                Tracking.is_active == True
            ).first()
            
            if tracking:
                for key, value in update_data.items():
                    setattr(tracking, key, value)
                session.flush()
            return tracking
        
        return self.execute_query(_update_tracking)
    
    # 违规相关操作
    def add_violation(self, violation_data: Dict) -> Violation:
        """添加违规记录
        
        Args:
            violation_data: 违规数据
            
        Returns:
            创建的违规对象
        """
        def _add_violation(session):
            violation = Violation(**violation_data)
            session.add(violation)
            session.flush()
            return violation
        
        return self.execute_query(_add_violation)
    
    def get_violations(self, skip: int = 0, limit: int = 100, date_filters: Dict = None, sort_by: str = 'created_at', sort_order: str = 'desc', **filters) -> List[Violation]:
        """获取违规记录
        
        Args:
            skip: 跳过记录数
            limit: 返回记录数
            date_filters: 日期过滤条件，包含start和end
            sort_by: 排序字段
            sort_order: 排序方向 (asc/desc)
            **filters: 其他过滤条件
            
        Returns:
            违规记录列表
        """
        def _get_violations(session):
            from sqlalchemy import desc, asc
            
            query = session.query(Violation).join(Camera)
            
            # 应用过滤条件
            if filters:
                query = query.filter_by(**filters)
            
            # 应用日期过滤
            if date_filters:
                if 'start' in date_filters:
                    query = query.filter(Violation.created_at >= date_filters['start'])
                if 'end' in date_filters:
                    query = query.filter(Violation.created_at < date_filters['end'])
            
            # 应用排序
            order_column = getattr(Violation, sort_by, Violation.created_at)
            if sort_order == 'desc':
                query = query.order_by(desc(order_column))
            else:
                query = query.order_by(asc(order_column))
            
            return query.offset(skip).limit(limit).all()
        
        return self.execute_query(_get_violations)
    
    def get_violation_by_id(self, violation_id: int) -> Optional[Violation]:
        """根据ID获取违规记录
        
        Args:
            violation_id: 违规记录ID
            
        Returns:
            违规记录对象，未找到则返回None
        """
        def _get_violation_by_id(session):
            return session.query(Violation).join(Camera).filter(Violation.id == violation_id).first()
        
        return self.execute_query(_get_violation_by_id)
    
    def update_violation(self, violation_id: int, **update_data) -> Optional[Violation]:
        """更新违规记录
        
        Args:
            violation_id: 违规记录ID
            **update_data: 更新数据
            
        Returns:
            更新后的违规记录对象，未找到则返回None
        """
        def _update_violation(session):
            from datetime import datetime
            
            violation = session.query(Violation).filter(Violation.id == violation_id).first()
            if violation:
                # 添加更新时间
                update_data['updated_at'] = datetime.now()
                
                # 应用更新
                for key, value in update_data.items():
                    if hasattr(violation, key):
                        setattr(violation, key, value)
                
                session.flush()
            return violation
        
        return self.execute_query(_update_violation)
    
    def confirm_violation(self, violation_id: int, confirmed: bool, confirmed_by: str, notes: str = '') -> Optional[Violation]:
        """确认违规记录
        
        Args:
            violation_id: 违规记录ID
            confirmed: 是否确认
            confirmed_by: 确认人
            notes: 确认备注
            
        Returns:
            更新后的违规记录对象，未找到则返回None
        """
        def _confirm_violation(session):
            from datetime import datetime
            
            violation = session.query(Violation).filter(Violation.id == violation_id).first()
            if violation:
                violation.confirmed = confirmed
                violation.confirmed_by = confirmed_by
                violation.confirmed_at = datetime.now()
                violation.notes = notes
                violation.updated_at = datetime.now()
                
                # 如果确认，自动设置状态为processed
                if confirmed:
                    violation.status = 'processed'
                
                session.flush()
            return violation
        
        return self.execute_query(_confirm_violation)
    
    def get_related_violations(self, violation_id: int, time_window: timedelta = timedelta(minutes=30), limit: int = 10) -> List[Violation]:
        """获取相关违规记录
        
        Args:
            violation_id: 基准违规记录ID
            time_window: 时间窗口
            limit: 返回记录数
            
        Returns:
            相关违规记录列表
        """
        def _get_related_violations(session):
            # 获取基准违规记录
            base_violation = session.query(Violation).filter(Violation.id == violation_id).first()
            if not base_violation:
                return []
            
            # 计算时间范围
            start_time = base_violation.created_at - time_window
            end_time = base_violation.created_at + time_window
            
            # 查询同一摄像头、相近时间的违规记录
            related = session.query(Violation).filter(
                Violation.camera_id == base_violation.camera_id,
                Violation.id != violation_id,
                Violation.created_at >= start_time,
                Violation.created_at <= end_time
            ).order_by(Violation.created_at.desc()).limit(limit).all()
            
            return related
        
        return self.execute_query(_get_related_violations)
    
    def count_violations(self, date_filters: Dict = None, **filters) -> int:
        """统计违规记录数量
        
        Args:
            date_filters: 日期过滤条件
            **filters: 其他过滤条件
            
        Returns:
            违规记录数量
        """
        def _count_violations(session):
            query = session.query(func.count(Violation.id))
            
            # 应用过滤条件
            if filters:
                query = query.filter_by(**filters)
            
            # 应用日期过滤
            if date_filters:
                if 'start' in date_filters:
                    query = query.filter(Violation.created_at >= date_filters['start'])
                if 'end' in date_filters:
                    query = query.filter(Violation.created_at < date_filters['end'])
            
            return query.scalar()
        
        from sqlalchemy import func
        return self.execute_query(_count_violations)
    
    def get_violations_statistics(self, days: int = 7, group_by: str = 'day', **filters) -> Dict[str, Any]:
        """获取违规统计信息
        
        Args:
            days: 统计天数
            group_by: 分组方式 (day/hour/type/camera/severity)
            **filters: 过滤条件
            
        Returns:
            统计数据
        """
        def _get_violations_statistics(session):
            from datetime import datetime, timedelta
            from sqlalchemy import func
            
            start_date = datetime.now() - timedelta(days=days)
            
            # 基础查询
            query = session.query(Violation).filter(Violation.created_at >= start_date)
            
            # 应用过滤条件
            if filters:
                query = query.filter_by(**filters)
            
            statistics = {}
            
            if group_by == 'day':
                # 按天分组
                daily_stats = session.query(
                    func.date(Violation.created_at).label('date'),
                    func.count(Violation.id).label('count')
                ).filter(Violation.created_at >= start_date)
                
                if filters:
                    daily_stats = daily_stats.filter_by(**filters)
                
                daily_stats = daily_stats.group_by(func.date(Violation.created_at)).all()
                statistics['daily'] = {str(stat.date): stat.count for stat in daily_stats}
                
            elif group_by == 'hour':
                # 按小时分组
                hourly_stats = session.query(
                    func.strftime('%Y-%m-%d %H:00', Violation.created_at).label('hour'),
                    func.count(Violation.id).label('count')
                ).filter(Violation.created_at >= start_date)
                
                if filters:
                    hourly_stats = hourly_stats.filter_by(**filters)
                
                hourly_stats = hourly_stats.group_by(
                    func.strftime('%Y-%m-%d %H:00', Violation.created_at)
                ).all()
                statistics['hourly'] = {stat.hour: stat.count for stat in hourly_stats}
                
            elif group_by == 'type':
                # 按违规类型分组
                type_stats = session.query(
                    Violation.violation_type,
                    func.count(Violation.id)
                ).filter(Violation.created_at >= start_date)
                
                if filters:
                    type_stats = type_stats.filter_by(**filters)
                
                type_stats = type_stats.group_by(Violation.violation_type).all()
                statistics['type'] = dict(type_stats)
                
            elif group_by == 'camera':
                # 按摄像头分组
                camera_stats = session.query(
                    Camera.name,
                    func.count(Violation.id)
                ).join(Violation).filter(Violation.created_at >= start_date)
                
                if filters:
                    camera_stats = camera_stats.filter_by(**filters)
                
                camera_stats = camera_stats.group_by(Camera.name).all()
                statistics['camera'] = dict(camera_stats)
                
            elif group_by == 'severity':
                # 按严重程度分组
                severity_stats = session.query(
                    Violation.severity,
                    func.count(Violation.id)
                ).filter(Violation.created_at >= start_date)
                
                if filters:
                    severity_stats = severity_stats.filter_by(**filters)
                
                severity_stats = severity_stats.group_by(Violation.severity).all()
                statistics['severity'] = dict(severity_stats)
            
            # 总体统计
            statistics['total'] = query.count()
            statistics['unique_types'] = query.distinct(Violation.violation_type).count()
            statistics['unique_cameras'] = query.distinct(Violation.camera_id).count()
            
            return statistics
        
        return self.execute_query(_get_violations_statistics)
    
    def get_violation_types(self) -> List[Dict[str, Any]]:
        """获取所有违规类型
        
        Returns:
            违规类型列表
        """
        def _get_violation_types(session):
            from sqlalchemy import func
            
            # 获取所有违规类型及其计数
            type_counts = session.query(
                Violation.violation_type,
                func.count(Violation.id).label('count')
            ).group_by(Violation.violation_type).all()
            
            # 预定义的违规类型描述
            type_descriptions = {
                'pedestrian_violation': '行人违规',
                'vehicle_overlap': '车辆重叠',
                'illegal_parking': '违章停车',
                'traffic_light_violation': '闯红灯',
                'speeding': '超速行驶',
                'lane_violation': '车道违规',
                'pedestrian_violation': '行人违规',
                'vehicle_violation': '车辆违规'
            }
            
            result = []
            for violation_type, count in type_counts:
                result.append({
                    'type': violation_type,
                    'description': type_descriptions.get(violation_type, violation_type),
                    'count': count
                })
            
            # 按计数降序排序
            result.sort(key=lambda x: x['count'], reverse=True)
            
            return result
        
        return self.execute_query(_get_violation_types)
    
    def export_violations(self, format: str = 'csv', date_filters: Dict = None, **filters) -> str:
        """导出违规记录
        
        Args:
            format: 导出格式 (csv/excel)
            date_filters: 日期过滤条件
            **filters: 其他过滤条件
            
        Returns:
            导出文件路径
        """
        def _export_violations(session):
            import csv
            import os
            from datetime import datetime
            
            # 获取违规记录
            query = session.query(Violation).join(Camera)
            
            # 应用过滤条件
            if filters:
                query = query.filter_by(**filters)
            
            # 应用日期过滤
            if date_filters:
                if 'start' in date_filters:
                    query = query.filter(Violation.created_at >= date_filters['start'])
                if 'end' in date_filters:
                    query = query.filter(Violation.created_at < date_filters['end'])
            
            violations = query.order_by(Violation.created_at.desc()).all()
            
            # 创建导出目录
            export_dir = get_config('export.path', './exports')
            os.makedirs(export_dir, exist_ok=True)
            
            # 生成文件名
            timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
            
            if format == 'csv':
                file_path = os.path.join(export_dir, f'violations_{timestamp}.csv')
                
                # 写入CSV文件
                with open(file_path, 'w', newline='', encoding='utf-8-sig') as csvfile:
                    fieldnames = ['ID', '违规类型', '严重程度', '描述', '摄像头名称', 
                                 '位置', '置信度', '状态', '创建时间', '更新时间', '处理人']
                    writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
                    
                    writer.writeheader()
                    for violation in violations:
                        writer.writerow({
                            'ID': violation.id,
                            '违规类型': violation.violation_type,
                            '严重程度': violation.severity,
                            '描述': violation.description,
                            '摄像头名称': violation.camera.name if violation.camera else '',
                            '位置': violation.location or '',
                            '置信度': violation.confidence or '',
                            '状态': violation.status,
                            '创建时间': violation.created_at.strftime('%Y-%m-%d %H:%M:%S') if violation.created_at else '',
                            '更新时间': violation.updated_at.strftime('%Y-%m-%d %H:%M:%S') if violation.updated_at else '',
                            '处理人': violation.processed_by or ''
                        })
                
            elif format == 'excel':
                try:
                    import pandas as pd
                    
                    file_path = os.path.join(export_dir, f'violations_{timestamp}.xlsx')
                    
                    # 准备数据
                    data = []
                    for violation in violations:
                        data.append({
                            'ID': violation.id,
                            '违规类型': violation.violation_type,
                            '严重程度': violation.severity,
                            '描述': violation.description,
                            '摄像头名称': violation.camera.name if violation.camera else '',
                            '位置': violation.location or '',
                            '置信度': violation.confidence or '',
                            '状态': violation.status,
                            '创建时间': violation.created_at if violation.created_at else None,
                            '更新时间': violation.updated_at if violation.updated_at else None,
                            '处理人': violation.processed_by or ''
                        })
                    
                    # 创建DataFrame并导出
                    df = pd.DataFrame(data)
                    with pd.ExcelWriter(file_path, engine='openpyxl') as writer:
                        df.to_excel(writer, index=False, sheet_name='违规记录')
                
                except ImportError:
                    # 如果没有pandas，降级到CSV格式
                    self.logger.warning("未安装pandas，使用CSV格式导出")
                    file_path = os.path.join(export_dir, f'violations_{timestamp}.csv')
                    
                    with open(file_path, 'w', newline='', encoding='utf-8-sig') as csvfile:
                        fieldnames = ['ID', '违规类型', '严重程度', '描述', '摄像头名称', 
                                     '位置', '置信度', '状态', '创建时间', '更新时间', '处理人']
                        writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
                        
                        writer.writeheader()
                        for violation in violations:
                            writer.writerow({
                                'ID': violation.id,
                                '违规类型': violation.violation_type,
                                '严重程度': violation.severity,
                                '描述': violation.description,
                                '摄像头名称': violation.camera.name if violation.camera else '',
                                '位置': violation.location or '',
                                '置信度': violation.confidence or '',
                                '状态': violation.status,
                                '创建时间': violation.created_at.strftime('%Y-%m-%d %H:%M:%S') if violation.created_at else '',
                                '更新时间': violation.updated_at.strftime('%Y-%m-%d %H:%M:%S') if violation.updated_at else '',
                                '处理人': violation.processed_by or ''
                            })
            
            return file_path
        
        from config import get_config
        return self.execute_query(_export_violations)
    
    # 告警相关操作
    def add_alert(self, alert_data: Dict) -> Alert:
        """添加告警记录
        
        Args:
            alert_data: 告警数据
            
        Returns:
            创建的告警对象
        """
        def _add_alert(session):
            alert = Alert(**alert_data)
            session.add(alert)
            session.flush()
            return alert
        
        return self.execute_query(_add_alert)
    
    def get_alerts(self, skip: int = 0, limit: int = 100, **filters):
        """获取告警记录
        
        Args:
            skip: 跳过记录数
            limit: 返回记录数
            **filters: 过滤条件
            
        Returns:
            告警记录列表（字典格式）
        """
        def _get_alerts(session):
            query = session.query(Alert)
            if filters:
                query = query.filter_by(**filters)
            alerts = query.order_by(Alert.created_at.desc()).offset(skip).limit(limit).all()
            
            # 在会话内部进行序列化，避免会话关闭后访问模型属性
            result = []
            for alert in alerts:
                result.append({
                    'id': alert.id,
                    'alert_id': alert.alert_id,
                    'alert_type': alert.alert_type,
                    'severity': alert.severity,
                    'source': alert.source or 'system',
                    'message': alert.message,
                    'created_at': alert.created_at.isoformat() if alert.created_at else None,
                    'is_acknowledged': alert.is_acknowledged,
                    'is_resolved': alert.is_resolved,
                    'snapshot_path': alert.snapshot_path
                })
            return result
        
        return self.execute_query(_get_alerts)
        
    def get_alert_by_id(self, alert_id):
        """根据ID获取单个告警
        
        参数:
            alert_id: 告警ID
            
        返回:
            告警信息字典或None
        """
        def _get_alert_by_id(session):
            alert = session.query(Alert).filter(Alert.id == alert_id).first()
            if alert:
                # 返回字典而不是ORM对象，避免detached状态问题
                return {
                    'id': alert.id,
                    'alert_id': alert.alert_id,
                    'alert_type': alert.alert_type,
                    'severity': alert.severity,
                    'source': alert.source,
                    'message': alert.message,
                    'details': alert.details,
                    'snapshot_path': alert.snapshot_path,
                    'violation_id': alert.violation_id,
                    'is_acknowledged': alert.is_acknowledged,
                    'acknowledged_by': alert.acknowledged_by,
                    'acknowledged_at': alert.acknowledged_at.isoformat() if alert.acknowledged_at else None,
                    'is_resolved': alert.is_resolved,
                    'resolved_at': alert.resolved_at.isoformat() if alert.resolved_at else None,
                    'created_at': alert.created_at.isoformat() if alert.created_at else None
                }
            return None
        
        return self.execute_query(_get_alert_by_id)
        
    def update_alert_acknowledged_status(self, alert_id: int, is_acknowledged: bool, user_id: str = None) -> bool:
        """更新告警确认状态
        
        参数:
            alert_id: 告警ID
            is_acknowledged: 是否已确认
            user_id: 确认用户ID
            
        返回:
            是否更新成功
        """
        def _update_alert_acknowledged_status(session):
            from datetime import datetime
            from datetime import timezone
            
            alert = session.query(Alert).filter(Alert.id == alert_id).first()
            if not alert:
                return False
            
            alert.is_acknowledged = is_acknowledged
            if is_acknowledged:
                alert.acknowledged_by = user_id or "system"
                alert.acknowledged_at = datetime.now(timezone.utc)
            
            # 确保提交更改到数据库
            session.commit()
            return True
        
        return self.execute_query(_update_alert_acknowledged_status)
        
    def batch_update_alert_acknowledged_status(self, alert_ids, is_acknowledged):
        """批量更新告警的确认状态
        
        参数:
            alert_ids: 告警ID列表
            is_acknowledged: 是否已确认
            
        返回:
            int: 更新的告警数量
        """
        def _batch_update_alert_acknowledged_status(session):
            result = session.query(Alert).filter(Alert.id.in_(alert_ids)).update(
                {Alert.is_acknowledged: is_acknowledged}, synchronize_session=False
            )
            return result
        
        return self.execute_query(_batch_update_alert_acknowledged_status)
        
    def confirm_alert(self, alert_id):
        """确认告警
        
        参数:
            alert_id: 告警ID
            
        返回:
            bool: 是否确认成功
        """
        def _confirm_alert(session):
            alert = session.query(Alert).filter(Alert.id == alert_id).first()
            if alert:
                alert.status = 'confirmed'
                alert.confirmed_at = datetime.utcnow()
                return True
            return False
        
        return self.execute_query(_confirm_alert)
        
    def batch_confirm_alerts(self, alert_ids):
        """批量确认告警
        
        参数:
            alert_ids: 告警ID列表
            
        返回:
            int: 确认的告警数量
        """
        def _batch_confirm_alerts(session):
            result = session.query(Alert).filter(Alert.id.in_(alert_ids)).update(
                {
                    Alert.status: 'confirmed',
                    Alert.confirmed_at: datetime.utcnow()
                }, 
                synchronize_session=False
            )
            return result
        
        return self.execute_query(_batch_confirm_alerts)
        
    def resolve_alert(self, alert_id):
        """解决告警
        
        参数:
            alert_id: 告警ID
            
        返回:
            bool: 是否解决成功
        """
        def _resolve_alert(session):
            alert = session.query(Alert).filter(Alert.id == alert_id).first()
            if alert:
                alert.status = 'resolved'
                alert.resolved_at = datetime.utcnow()
                return True
            return False
        
        return self.execute_query(_resolve_alert)
        
    def get_alert_history(self, alert_id: int):
        """获取告警历史记录
        
        Args:
            alert_id: 告警ID
            
        Returns:
            告警历史记录列表
        """
        def _get_alert_history(session):
            from .models import AlertHistory
            return session.query(AlertHistory).filter(
                AlertHistory.alert_id == alert_id
            ).order_by(AlertHistory.timestamp.desc()).all()
        
        return self.execute_query(_get_alert_history)
    
    # 系统日志相关操作
    def add_system_log(self, level: str, component: str, message: str, 
                      details: Dict = None, stack_trace: str = None, metadata: Dict = None) -> SystemLog:
        """添加系统日志
        
        Args:
            level: 日志级别
            component: 组件名称
            message: 日志消息
            details: 详细信息
            stack_trace: 堆栈跟踪
            metadata: 元数据，存储额外的上下文信息
            
        Returns:
            创建的系统日志对象
        """
        log_data = {
            'level': level,
            'component': component,
            'message': message,
            'details': details,
            'stack_trace': stack_trace,
            'metadata': metadata
        }
        
        def _add_system_log(session):
            log = SystemLog(**log_data)
            session.add(log)
            session.flush()
            return log
        
        return self.execute_query(_add_system_log)
    
    # 用户相关操作
    def authenticate_user(self, username: str, password: str) -> Optional[User]:
        """用户认证
        
        Args:
            username: 用户名
            password: 密码
            
        Returns:
            认证成功返回用户对象，失败返回None
        """
        def _authenticate_user(session):
            user = session.query(User).filter(User.username == username).first()
            if user and user.status == 'active':
                try:
                    from passlib.hash import bcrypt
                    if bcrypt.verify(password, user.password_hash):
                        # 更新最后登录时间
                        from datetime import datetime
                        user.last_login = datetime.now()
                        session.flush()
                        return user
                except ImportError:
                    # 如果没有passlib，使用简单比较（不推荐用于生产环境）
                    if user.password_hash == password:
                        return user
            return None
        
        return self.execute_query(_authenticate_user)
    
    # 配置相关操作
    def get_config(self, key: str) -> Optional[Any]:
        """获取配置值
        
        Args:
            key: 配置键
            
        Returns:
            配置值，未找到则返回None
        """
        def _get_config(session):
            config = session.query(Config).filter(
                Config.key == key,
                Config.is_active == True
            ).first()
            return config.value if config else None
        
        return self.execute_query(_get_config)
    
    def set_config(self, key: str, value: Any, description: str = None, 
                  config_type: str = 'general') -> Config:
        """设置配置值
        
        Args:
            key: 配置键
            value: 配置值
            description: 配置描述
            config_type: 配置类型
            
        Returns:
            创建或更新的配置对象
        """
        def _set_config(session):
            config = session.query(Config).filter(Config.key == key).first()
            if config:
                config.value = value
                config.description = description or config.description
                config.config_type = config_type or config.config_type
                config.is_active = True
            else:
                config = Config(
                    key=key,
                    value=value,
                    description=description,
                    config_type=config_type
                )
                session.add(config)
            session.flush()
            return config
        
        return self.execute_query(_set_config)
    
    # 数据库维护相关操作
    def clean_old_data(self, days: int = 30) -> Dict[str, int]:
        """清理旧数据
        
        Args:
            days: 保留天数
            
        Returns:
            各表清理的记录数
        """
        from datetime import datetime, timedelta
        cutoff_date = datetime.now() - timedelta(days=days)
        
        def _clean_old_data(session):
            deleted_counts = {}
            
            # 清理旧的检测记录
            deleted_detections = session.query(Detection).filter(
                Detection.created_at < cutoff_date
            ).delete()
            deleted_counts['detections'] = deleted_detections
            
            # 清理已结束的跟踪记录
            deleted_tracking = session.query(Tracking).filter(
                Tracking.is_active == False,
                Tracking.end_time < cutoff_date
            ).delete()
            deleted_counts['tracking'] = deleted_tracking
            
            # 清理已处理的违规记录
            deleted_violations = session.query(Violation).filter(
                Violation.status.in_(['processed', 'dismissed']),
                Violation.created_at < cutoff_date
            ).delete()
            deleted_counts['violations'] = deleted_violations
            
            # 清理已解决的告警记录
            deleted_alerts = session.query(Alert).filter(
                Alert.is_resolved == True,
                Alert.created_at < cutoff_date
            ).delete()
            deleted_counts['alerts'] = deleted_alerts
            
            # 清理旧系统日志
            deleted_logs = session.query(SystemLog).filter(
                SystemLog.created_at < cutoff_date
            ).delete()
            deleted_counts['system_logs'] = deleted_logs
            
            return deleted_counts
        
        result = self.execute_query(_clean_old_data)
        self.logger.info(f"数据清理完成: {result}")
        return result
    
    def get_statistics(self, days: int = 7) -> Dict[str, Any]:
        """获取系统统计信息
        
        Args:
            days: 统计天数
            
        Returns:
            统计信息字典
        """
        from datetime import datetime, timedelta
        start_date = datetime.now() - timedelta(days=days)
        
        def _get_statistics(session):
            stats = {}
            
            # 活跃摄像头数
            stats['active_cameras'] = session.query(Camera).filter(
                Camera.status == 'active'
            ).count()
            
            # 今日违规数
            today_start = datetime.now().replace(hour=0, minute=0, second=0, microsecond=0)
            stats['today_violations'] = session.query(Violation).filter(
                Violation.created_at >= today_start
            ).count()
            
            # 最近N天违规数
            stats['recent_violations'] = session.query(Violation).filter(
                Violation.created_at >= start_date
            ).count()
            
            # 违规类型分布
            violation_types = session.query(
                Violation.violation_type,
                func.count(Violation.id)
            ).filter(
                Violation.created_at >= start_date
            ).group_by(Violation.violation_type).all()
            stats['violation_type_distribution'] = dict(violation_types)
            
            # 告警统计
            stats['recent_alerts'] = session.query(Alert).filter(
                Alert.created_at >= start_date
            ).count()
            
            return stats
        
        # 需要导入func
        from sqlalchemy import func
        return self.execute_query(_get_statistics)
        
    def get_statistics_data(self, start_time: datetime, end_time: datetime, camera_id: str = None, realtime: bool = False) -> Dict[str, Any]:
        """从数据库获取统计数据
        
        Args:
            start_time: 开始时间
            end_time: 结束时间
            camera_id: 摄像头ID，可选
            realtime: 是否为实时数据
            
        Returns:
            统计数据字典
        """
        try:
            # 从数据库获取真实数据
            from sqlalchemy import func
            
            def _query_statistics_data(session):
                # 构建查询条件
                query = session.query(
                    func.count(Alert.id).label('total_alerts'),
                    func.count(func.distinct(Violation.vehicle_id)).label('total_vehicles'),
                    Alert.violation_type,
                    Camera.id.label('camera_id'),
                    Camera.name.label('camera_name')
                ).join(
                    Violation, Alert.violation_id == Violation.id
                ).join(
                    Camera, Alert.camera_id == Camera.id
                ).filter(
                    Alert.created_at >= start_time,
                    Alert.created_at <= end_time
                )
                
                # 如果指定了摄像头ID，则添加过滤条件
                if camera_id:
                    query = query.filter(Alert.camera_id == camera_id)
                
                # 执行查询，获取原始数据
                results = query.all()
                
                # 统计总告警数和总车辆数
                total_alerts = sum(r.total_alerts for r in results) if results else 0
                total_vehicles = sum(r.total_vehicles for r in results) if results else 0
                
                # 统计各摄像头数据
                camera_stats = []
                camera_dict = {}
                
                for r in results:
                    if r.camera_id not in camera_dict:
                        camera_dict[r.camera_id] = {
                            'camera_id': r.camera_id,
                            'camera_name': r.camera_name,
                            'total_alerts': 0,
                            'red_light': 0,
                            'wrong_way': 0,
                            'speeding': 0,
                            'illegal_parking': 0
                        }
                    
                    camera_dict[r.camera_id]['total_alerts'] += r.total_alerts
                    if r.violation_type:
                        violation_type_key = r.violation_type.lower().replace(' ', '_')
                        if violation_type_key in camera_dict[r.camera_id]:
                            camera_dict[r.camera_id][violation_type_key] += r.total_alerts
                
                camera_stats = list(camera_dict.values())
                
                # 计算平均流量
                time_diff_hours = (end_time - start_time).total_seconds() / 3600
                average_flow_rate = total_vehicles / time_diff_hours if time_diff_hours > 0 else 0
                
                # 构建告警趋势数据（每小时）
                alert_trend = []
                current_time = start_time.replace(minute=0, second=0, microsecond=0)
                while current_time <= end_time:
                    next_hour = current_time + timedelta(hours=1)
                    hour_query = session.query(func.count(Alert.id)).filter(
                        Alert.created_at >= current_time,
                        Alert.created_at < next_hour
                    )
                    if camera_id:
                        hour_query = hour_query.filter(Alert.camera_id == camera_id)
                    hour_count = hour_query.scalar() or 0
                    
                    alert_trend.append({
                        'time': current_time.strftime('%Y-%m-%d %H:%M:%S'),
                        'count': hour_count
                    })
                    current_time = next_hour
                
                # 构建违规类型分布
                violation_types = []
                violation_query = session.query(
                    Alert.violation_type,
                    func.count(Alert.id).label('count')
                ).filter(
                    Alert.created_at >= start_time,
                    Alert.created_at <= end_time
                )
                if camera_id:
                    violation_query = violation_query.filter(Alert.camera_id == camera_id)
                violation_results = violation_query.group_by(Alert.violation_type).all()
                
                for v in violation_results:
                    violation_types.append({
                        'type': v.violation_type,
                        'count': v.count
                    })
                
                # 返回标准格式的数据
                return {
                    'total_alerts': total_alerts,
                    'total_vehicles': total_vehicles,
                    'average_flow_rate': average_flow_rate,
                    'camera_stats': camera_stats,
                    'alert_trend': alert_trend,
                    'violation_types': violation_types,
                    'time_distribution': []  # 可以根据需要补充
                }
            
            # 执行查询
            statistics_data = self.execute_query(_query_statistics_data)
            
            # 记录统计查询
            self.logger.info(f"从数据库成功获取统计数据: {start_time} 到 {end_time}, 告警数: {statistics_data.get('total_alerts', 0)}")
            
            return statistics_data
            
        except Exception as e:
            self.logger.error(f"从数据库获取统计数据失败: {str(e)}")
            # 返回空的统计数据结构，但不引入循环依赖
            return {
                'total_alerts': 0,
                'total_vehicles': 0,
                'average_flow_rate': 0,
                'camera_stats': [],
                'alert_trend': [],
                'violation_types': [],
                'time_distribution': []
            }
    
    def backup_database(self, backup_path: str = None) -> str:
        """备份数据库
        
        Args:
            backup_path: 备份文件路径
            
        Returns:
            备份文件路径
        """
        import shutil
        from datetime import datetime
        
        if self.db_type == 'sqlite':
            if not backup_path:
                # 生成默认备份路径
                db_dir = os.path.dirname(self.db_url.replace('sqlite:///', ''))
                backup_dir = os.path.join(db_dir, 'backups')
                os.makedirs(backup_dir, exist_ok=True)
                
                timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
                backup_path = os.path.join(backup_dir, f'traffic_system_backup_{timestamp}.db')
            
            try:
                # 复制SQLite数据库文件
                db_file = self.db_url.replace('sqlite:///', '')
                shutil.copy2(db_file, backup_path)
                self.logger.info(f"数据库备份成功: {backup_path}")
                return backup_path
            except Exception as e:
                self.logger.error(f"数据库备份失败: {str(e)}")
                raise
        else:
            # 其他数据库类型的备份逻辑可以在这里扩展
            self.logger.warning(f"数据库类型 {self.db_type} 的备份功能暂未实现")
            return None

    def log_system_event(self, message: str, source: str, level: str = 'info', details: Dict = None) -> SystemLog:
        """记录系统事件（兼容旧版本调用）
        
        Args:
            message: 事件消息
            source: 事件来源
            level: 日志级别
            details: 详细信息
            
        Returns:
            创建的系统日志对象
        """
        # 适配到新的add_system_log格式
        return self.add_system_log(
            level=level.upper(),
            component=source,
            message=message,
            details=details,
            metadata={'source': source}
        )

# 创建全局数据库服务实例
db_service = DatabaseService()

# 导出常用方法
def get_db_service() -> DatabaseService:
    """获取数据库服务实例的快捷方法"""
    return db_service

def get_statistics_data(start_time: datetime, end_time: datetime, camera_id: str = None, realtime: bool = False) -> Dict[str, Any]:
    """
    获取统计数据
    
    Args:
        start_time: 开始时间
        end_time: 结束时间
        camera_id: 摄像头ID（可选）
        realtime: 是否为实时数据
    
    Returns:
        统计数据字典
    """
    return db_service.get_statistics_data(start_time, end_time, camera_id, realtime)